﻿# coding:utf-8


"""
处理白名单的增删改查
"""
try :
    import cx_Oracle
    user = 'ats'
    passwd = 'ats'
    # orcl = 'localhost:1521/hdrocl.webserver'
    orcl = '192.168.15.45/lytzats'
    conn = cx_Oracle.connect(user, passwd, orcl)
except Exception :
    # import MySQLdb
    # conn = sqlite3.connect('db.sqlite3')

    # conn= MySQLdb.connect(
    #         host='localhost',
    #         port = 3306,
    #         user='root',
    #         passwd='lytz888',
    #         db ='mysite',
    #         )

    import sqlite3
    conn = sqlite3.connect('db.test')


import xlwt
from django.http import StreamingHttpResponse
from django.http import HttpResponse
import os

# 设置环境变量，解决oracle字符问题
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'


def init():
    cursor = conn.cursor()
    sql = "select t.product_id, t.product_name from cm_product_info t"
    cursor.execute(sql)

    res = [{'product_id' : i[0], 'product_name' : i[1].decode('gbk')} for i in cursor.fetchall()]

    cursor.close()

    return res

def add(invest_id, stock_code):

    cursor = conn.cursor()
    sql = "insert into cm_risk_white_rule values(%s, '%s', seq_cm_id.nextval)" % (invest_id, stock_code)

    cursor.execute(sql)
    conn.commit()
    cursor.close()


def delete(rule_id):

    cursor = conn.cursor()
    sql = "delete cm_risk_white_rule where rule_id = %s" % rule_id
    cursor.execute(sql)
    conn.commit()
    cursor.close()


def modify(invest_id, stock_code, rule_id):

    cursor = conn.cursor()
    sql = "update cm_risk_white_rule set invest_id = %s, stock_code='%s'  where rule_id = %s" \
          % (invest_id, stock_code, rule_id)
    cursor.execute(sql)
    conn.commit()
    cursor.close()


def query(invest_id=None, stock_code=None, rule_id=None):

    cond = ''
    if invest_id:
        cond += 'invest_id like %s and ' % invest_id
    if stock_code:
        cond += "stock_code like '%s%%' and " % stock_code
    if rule_id:
        cond += 'rule_id = %s and ' % rule_id
    if cond != '':
        cond = 'where ' + cond[:-5]


    cursor = conn.cursor()
    sql = "select * from cm_risk_white_rule " + cond


    cursor.execute(sql)

    res = cursor.fetchall()

    cursor.close()

    return res


def export_excel():
    print "excel_export"
    # 创建 Workbook 时，如果需要写入中文，请使用 utf-8 编码，默认是 unicode 编码。
    wb = xlwt.Workbook(encoding='utf-8')
    ws = wb.add_sheet('配件价格')
    ws.write(0, 0, '产品编号')
    ws.write(0, 1, '产品名称')
    ws.write(0, 2, '产品经理')
    ws.write(0, 3, '产品规模')
    ws.write(0, 4, '产品状态')
    ws.write(0, 5, '创建时间')

    # ------ 开始：这段代码可以用下面注释段替代，都是本应保存为文件格式的改成保存为数据流，以便返回前端下载
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename=example.xls'
    wb.save(response)
    return response


def file_upload(request):
    print "file_upload"
    filename = request.FILES["fileImport"]
    UploadedFile = request.FILES["fileImport"]
    f = UploadedFile.read()
    name = UploadedFile.name
    BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
    the_file_name = BASE_DIR + "/upload/" + name
    destination = open(the_file_name,'wb+')
    for chunk in UploadedFile.chunks():
        destination.write(chunk)
    destination.close()
    return HttpResponse('file_upload')


def file_download(request):
    # do something...
    print "file_download"
    def file_iterator(file_name, chunk_size=512):
            f = open(file_name, "rb")
            while True:
                c = f.read(chunk_size)
                if c:
                    yield c
                else:
                    break
    BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))
    filename = u"测试testfile.txt"
    the_file_name = BASE_DIR + "/upload/" + filename
    response = HttpResponse(file_iterator(the_file_name), content_type='APPLICATION/OCTET-STREAM') #设定文件头，这种设定可以让任意文件都能正确下载，而且已知文本文件不是本地打开

    response['Content-Disposition'] = 'attachment; filename='+filename.encode('utf-8')#设定传输给客户端的文件名称
    response['Content-Length'] = os.path.getsize(the_file_name)#传输给客户端的文件大小
    print response
    return response

